<?php 
	class Courses extends CI_Model{

	 	function __construct(){
        	parent::__construct();
    	}

        /**
             * 发布视频课程
             * @param  [type] $course_id [description]
             * @return [type]            [description]
             */
            public function publish_course_video($school_id,$user_id,$data)
            {
                //开始事务
            $this->db->trans_start();

                $course = array(
              'uid' => $user_id,
              'school_id' => $school_id,
              'cate_id' => $data['cate_id'],
              'subject_id' => $data['subject_id'],
              'type' => 1,
              'title' => $data['title'],
              'course_img' => !empty($data['img_url'])?$data['img_url'][0]:'',
              'course_imgs' => !empty($data['img_url'])?json_encode($data['img_url']):'',
              'detail' => $data['detail'],
              'price' => $data['price'],
              'course_time' => $data['course_time'],
              'class_number' => 0,
              'suitable' => $data['suitable'],
              'learn_what' => $data['learn_what'],
              'click_num' => 0,
              'status' => 1,
              'sales' => 0,
              'is_recomend' => 0,
              'haoping' => 0,
              'zhongping' => 0,
              'chaping' => 0,
              'comment_count' => 0,
              'add_time' => time(),
            );
            //添加课程
            $this->db->insert('course',$course);
            $course_id = $this->db->insert_id();
            $this->db->trans_complete();
            return $course_id;
        }

        function get_course_all($limit=0,$offset=10,$school_id = 0,$course_type=0){
            $this->db->trans_start();

            $where = " 1=1 ";
            if($school_id > 0 ){
                $where .= " and c.school_id=$school_id ";
            }else{
                $where .= " and c.school_id != 0 ";
            }
            if($course_type > 0){
                $where .= " and c.type=$course_type ";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS c.*,s.school_name FROM {$this->db->dbprefix}course as c left join {$this->db->dbprefix}o2o_school s on s.id = c.school_id where $where order by id desc limit $limit, $offset";

            $result = $this->db->query($sql)->result_array();

            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();

            $this->db->trans_complete();

            return array("total_count" => $row_num['row_num'], "result" => $result);
        }

    	function get_subject(){
    		$sql = "SELECT * FROM {$this->db->dbprefix}b_subject";
    		$result = $this->db->query($sql)->result_array();
    		return $result;
    	}

        /**
         * 获取视频课分类
         * @return [type] [description]
         */
        function get_cate(){
            $sql = "select * from {$this->db->dbprefix}course_cate where school_id = 0 and type=1";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_course_chapter($id){
            $sql = "SELECT pid from {$this->db->dbprefix}course_chapter where id = $id";
            $result = $this->db->query($sql)->row_array();
            if(isset($result['pid']) && $result['pid'] > 0){
                $this->db->where('id',$result['pid']);
                $result = $this->db->delete('course_chapter');
                if($result){
                    return true;
                }else{
                    return false;
                }
            }else{
                return true;
            }
        }


        function get_teacher(){
            $sql = "SELECT * FROM {$this->db->dbprefix}o2o_teachers where school_id = 0";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }


        function get_cate_list($search,$offset,$limit){

            $school_id = 0;
            if(!$search){
                $where = "school_id=$school_id";
            }else{
                $where = "school_id=$school_id and name like '%".$search."%'";
            }
            $where .= " and type=1 ";
            $sql = "select * from {$this->db->dbprefix}course_cate where  ".$where." order by id desc limit $limit,$offset";
            $result = $this->db->query($sql)->result_array();

            $sql = "select count(id) as count from {$this->db->dbprefix}course_cate where ".$where." order by sort asc";
            $count = $this->db->query($sql)->row_array();
            return  array('result' => $result, 'count'=>$count['count']);
        }

        /**
         * 获取视频课科目list
         * @param  [type] $search [description]
         * @param  [type] $offset [description]
         * @param  [type] $limit  [description]
         * @return [type]         [description]
         */
        function get_cate_subjects_list($search,$offset,$limit){

            $school_id = 0;
            if(!$search){
                $where = "sub.school_id=$school_id";
            }else{
                $where = "sub.school_id=$school_id and sub.name like '%".$search."%'";
            }
            $where .= " and sub.type=1 ";

            $this->db->trans_start();

            $sql = "select SQL_CALC_FOUND_ROWS sub.*,cate.name as cate_name from {$this->db->dbprefix}course_cate_subject sub left join {$this->db->dbprefix}course_cate cate on cate.id=sub.pid  where ".$where." order by id desc limit $limit,$offset";
            $result = $this->db->query($sql)->result_array();

            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();

            $this->db->trans_complete();

            return  array('result' => $result, 'count'=>$row_num['row_num']);
        }
    	

        function get_cate_info($id = 0){
            $sql = "SELECT * from {$this->db->dbprefix}course_cate where id = $id";
            return $this->db->query($sql)->row_array();
        }

        function get_cate_subject_info($id = 0){
            $sql = "SELECT * from {$this->db->dbprefix}course_cate_subject where id = $id";
            return $this->db->query($sql)->row_array();
        }


        /**
        * 函数功能介绍：返回课程列表（用于分页显示）
        * 创建人:  Yang Ningshu
        * @param id int
        * @return array
        */
        function get_list_ofpage($limit = 0, $offset = 10,$data = 0){
            $where = "1=1 and v.school_id = 0";
            $order = "v.id desc";

            if($data['status'] != 10){
                $where .= " AND v.status = '". $data['status']."'";
            }

            if($data['type'] > 0){
                $where .= " AND v.type = '". $data['type']."'";
            }

            if(isset($data['cate_id']) && $data['cate_id'] > 0){
                $where .= " AND v.cate_id = '". $data['cate_id']."'";
            }
            
            if(isset($data['kemu']) && $data['kemu'] > 0){
                $where .= " AND v.subject_id = '". $data['kemu']."'";
            }
            
            if(!empty($data['search'])) {
                $where .= " AND v.title like '%".$data['search']."%'";
            }

            $sql = "SELECT v.*,cate.name as cate_name FROM {$this->db->dbprefix}course as v left join {$this->db->dbprefix}course_cate cate on cate.id=v.cate_id  WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();

            //搜索标红
            if(isset($data['search']) && $data['search']){
                foreach ($result as $key => $value) {
                    $result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
                }
            }
            $sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}course as v WHERE $where";
            $count = $this->db->query($sql)->result();
            return array("count" => $count[0]->count, "result" => $result);
        }



        /**
        * 函数功能介绍：返回视频列表（用于分页显示）
        * 创建人:  Yang Ningshu
        * 创建时间: 2014-08-21 11:08
        * 最后修改人：Zhang ShaoWei
        * 最后修改时间：2014-08-29 15:08
        * @param id int
        * @return array
        */
        function get_video_list_ofpage($limit = 0, $offset = 10,$data = 0){
            $where = "1=1";
            if($data['sort'] == 1 and $data['asc_des'] == 2){
                $order = "v.baidu_recommended asc";
            }else if($data['sort'] == 1 and $data['asc_des'] == 1){
                $order = "v.baidu_recommended desc";
            }else if($data['sort'] == 2 and $data['asc_des'] == 2){
                $order = "v.click_num asc";
            }else if($data['sort'] == 2 and $data['asc_des'] == 1){
                $order = "v.click_num desc";
            }else if($data['sort'] == 3 and $data['asc_des'] == 2){
                $order = "v.wap_recommended asc";
            }else if($data['sort'] == 3 and $data['asc_des'] == 1){
                $order = "v.wap_recommended desc";
            }else if($data['sort'] == 4 and $data['asc_des'] == 2){
                $order = "v.add_time asc";
            }else if($data['sort'] == 4 and $data['asc_des'] == 1){
                $order = "v.add_time desc";
            }else if($data['sort'] == 5 and $data['asc_des'] == 2){
                $order = "v.status asc";
            }else if($data['sort'] == 5 and $data['asc_des'] == 1){
                $order = "v.status desc";
            }else if($data['sort'] == 6 and $data['asc_des'] == 2){
                $order = "v.sort asc";
            }else if($data['sort'] == 6 and $data['asc_des'] == 1){
                $order = "v.sort desc";
            }else{
                $order = "v.id desc";
            }
            if(isset($data['aid'])){
                $sql = "SELECT video_id FROM {$this->db->dbprefix}activity  WHERE id=".$data['aid']."";
                $result = $this->db->query($sql)->result_array();
                if($result[0]['video_id']){
                    $where.=" and v.id in (".$result[0]['video_id'].")";
                }else{
                    $result[0]['video_id'] = 0;
                    $where.=" and v.id in (".$result[0]['video_id'].")";
                }
            }

            if($data['review'] && $data['review'] > 0){
                $where .= " AND v.review = '". $data['review']."'";
            }
            
            if($data['cate_id'] && $data['cate_id'] > 0){
                $where .= " AND v.cate_id = '". $data['cate_id']."'";
            }
            
            if(!empty($data['search'])) {
                $where .= " AND v.title like '%".$data['search']."%'";
            }

            $sql = "SELECT v.id,v.cate_id,v.uid,v.video_size,v.image_url,v.review,v.duration,v.title,v.click_num,v.add_time,v.sort,v.status, u.nick FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();
            if($data['search']){
                foreach ($result as $key => $value) {
                    $result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
                }
            }
            $sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid WHERE $where";
            $count = $this->db->query($sql)->result();
            return array("count" => $count[0]->count, "result" => $result);
        }


        function get_course_info($id = 0){
            $sql = "SELECT * from {$this->db->dbprefix}course where type = 1 and id = $id and school_id = 0";
            return $this->db->query($sql)->row_array();
        }



        function check_is_sj($id = 0,$uid = 0){
            $sql = "SELECT id from {$this->db->dbprefix}course_shelves where course_id=$id and school_id = $uid";
            return $this->db->query($sql)->row_array();
        }


        function get_chapter_list($id){
            $sql = "SELECT * from {$this->db->dbprefix}course_chapter where pid = 0 and course_id = $id";
            $result = $this->db->query($sql)->result_array();
            if($result){
                foreach ($result as $key => $value) {
                    $sql = "SELECT * from {$this->db->dbprefix}course_chapter where pid = ".$value['id']." and course_id = $id";
                    $results = $this->db->query($sql)->result_array();
                    $result[$key]['child'] = $results;
                }
            }
            return $result;
        }


        function get_course_cate_subject($id){
            $id = (int)$id;
            $sql = "SELECT * from {$this->db->dbprefix}course_cate_subject where pid = $id";
            $results = $this->db->query($sql)->result_array();
            return $results;
        }
	}